package es.aradesh.ministryassist.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {
	private static final String TAG = "DatabaseHelper";
	private static final int DATABASE_VERSION = 5;
	private Context mContext;

	/**
	 * Database creation sql statement
	 */
	// private static final String DATABASE_CREATE =
	// "create table tjpredi_ (_id integer primary key autoincrement, "
	// + "title text not null, body text not null);";
	private static final String CREATE_TABLE = "CREATE TABLE ";
	private static final String TYPE_INTEGER = " INTEGER";
	private static final String TYPE_TEXT = " TEXT";
	private static final String TYPE_DATE = " DATE";
	private static final String TYPE_INTEGER_KEY_INC = " INTEGER PRIMARY KEY AUTOINCREMENT";
	private static final String NOT_NULL = " NOT NULL";
	private static final String COMMA = ",";
	
	DatabaseHelper(Context context) {
		super(context, DbAdapter.DATABASE_NAME, null, DATABASE_VERSION);
		this.mContext = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		// db.execSQL(CREATE_TABLE_TERRITORY);
		db.execSQL(CREATE_TABLE + DbAdapter.TABLE_TERRITORY + "("
				+ TerritoryColumns._ID + TYPE_INTEGER_KEY_INC + COMMA
				+ TerritoryColumns.NUMBER + TYPE_INTEGER + NOT_NULL + COMMA 
				+ TerritoryColumns.TOWN	+ TYPE_TEXT + NOT_NULL
				+ ");");
		
		//db.execSQL(CREATE_TABLE_STREET);
		db.execSQL(CREATE_TABLE + DbAdapter.TABLE_STREET + "("
				+ StreetColumns._ID  + TYPE_INTEGER_KEY_INC + COMMA
				+ StreetColumns.TERRITORY_ID + TYPE_INTEGER + " REFERENCES " + DbAdapter.TABLE_TERRITORY + "(" + TerritoryColumns._ID + ")" + COMMA
				+ StreetColumns.NAME + TYPE_TEXT + NOT_NULL + COMMA
				+ StreetColumns.TYPE + TYPE_INTEGER + " (0,3)" +NOT_NULL
				+ ");"); 
		//db.execSQL(CREATE_TABLE_HOME);
		db.execSQL(CREATE_TABLE + DbAdapter.TABLE_HOME +"("
				+ HomeColumns._ID + TYPE_INTEGER_KEY_INC + COMMA
				+ HomeColumns.STREET_ID + TYPE_INTEGER + " REFERENCES " + DbAdapter.TABLE_STREET + "(" + StreetColumns._ID + ")" + COMMA
				+ HomeColumns.BUILDING_ID + TYPE_INTEGER + " REFERENCES " + DbAdapter.TABLE_HOME + "(" + HomeColumns._ID + ")" + COMMA
				+ HomeColumns.NAME +TYPE_TEXT + NOT_NULL + COMMA
				+ HomeColumns.STATUS + TYPE_INTEGER + NOT_NULL + COMMA
				+ HomeColumns.OWNER + TYPE_TEXT + COMMA
				+ HomeColumns.LEVELS + TYPE_INTEGER + NOT_NULL + COMMA
				+ HomeColumns.DOORS + TYPE_INTEGER + NOT_NULL //+ COMMA
				+ ");"); 
		
		//db.execSQL(CREATE_TABLE_NOTES);
		db.execSQL(CREATE_TABLE + DbAdapter.TABLE_NOTES +"("
				+ NoteColumns._ID + TYPE_INTEGER_KEY_INC + COMMA
				+ NoteColumns.HOME_ID + TYPE_INTEGER + " REFERENCES " + DbAdapter.TABLE_HOME + "(" + HomeColumns._ID + ")" + COMMA
				+ NoteColumns.TYPE + TYPE_INTEGER + NOT_NULL + COMMA
				+ NoteColumns.DESCRIPTION + TYPE_TEXT + COMMA
				+ NoteColumns.CREATIONDATE + TYPE_DATE + NOT_NULL //+ COMMA
				+ ");");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
				+ newVersion + ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS " + DbAdapter.TABLE_TERRITORY);
		db.execSQL("DROP TABLE IF EXISTS " + DbAdapter.TABLE_STREET);
		//db.execSQL("DROP TABLE IF EXISTS " + DbAdapter.TABLE_BUILDING);
		db.execSQL("DROP TABLE IF EXISTS " + DbAdapter.TABLE_HOME);
		db.execSQL("DROP TABLE IF EXISTS " + DbAdapter.TABLE_NOTES);
		onCreate(db);
	}
}
